import pandas as pd
import os

def get_product_service_from_folder(folder_path, company_name):
    for filename in os.listdir(folder_path):
        if filename.endswith('.csv'):
            file_path = os.path.join(folder_path, filename)
            df = pd.read_csv(file_path)
            if 'Company_Name' in df.columns and 'Product_Service' in df.columns:
                match = df[df['Company_Name'] == company_name]
                if not match.empty:
                    return match.iloc[0]['Product_Service']
    return None

def merge_tables(table1_path, folder2_path):
    # 读取表格1
    table1 = pd.read_csv(table1_path)

    print("原始表格1:")
    print(table1)

    # 初始化一个新的列来存储产品与服务
    table1['Product_Service'] = ''

    # 遍历表格1中的每一行
    for index, row in table1.iterrows():
        company_name = row['Company_Name']
        product_service = get_product_service_from_folder(folder2_path, company_name)
        if product_service:
            table1.at[index, 'Product_Service'] = product_service

    print("\n合并后的表格1:")
    print(table1)

    # 将合并后的表格保存到新的CSV文件
    merged_table_path = 'merged_table1.csv'
    table1.to_csv(merged_table_path, index=False)
    print(f"\n合并后的表格已保存到 {merged_table_path}")

if __name__ == "__main__":
    table1_path = 'table1.csv'
    folder2_path = 'folder2'

    merge_tables(table1_path, folder2_path)